package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import view.DataList;

public class Student extends DBConnect {
    private final static String table = "student";
    public Student() {
    }

    public static int insert(String no, String name,
            String pwd, int _class) throws SQLException {
        PreparedStatement stmt = getPrepareStatement(
                "INSERT INTO `" + table + "` " + 
                "(`no`, `name`, `password`, `class`) VALUES (?, ?, ?, ?)");
        stmt.setString(1, no);
        stmt.setString(2, name);
        stmt.setString(3, pwd);
        stmt.setInt(4, _class);
        return stmt.executeUpdate();
    }

    public static PreparedStatement update(String where, String... feilds)
            throws SQLException {
        String sql = "UPDATE `" + table + "` " + "SET ";
        for (int i = 0; i < feilds.length; i++) {
            sql += "`" + feilds[i] + "`=?";
            if (i != feilds.length - 1)
                sql += ",";
        }
        sql += " WHERE " + where;
        return getPrepareStatement(sql);
    }

    public static DataList<Data> select() throws SQLException {
        ResultSet rs = createStatement()
            .executeQuery(
                    "SELECT " + Data.fields +
                    " FROM `" + table + "`");
        DataList<Data> data = new DataList<Data>();
        while (rs.next()) {
            data.add(Data.getData(rs));
        }
        return data;
    }

    public static Data findById(long id) throws SQLException {
        PreparedStatement stmt =
            getPrepareStatement(
                    "SELECT " + Data.fields +
                    " FROM `" + table +
                    "` where id=?");
        stmt.setLong(1, id);
        ResultSet rs = stmt.executeQuery();
        return rs.next() ? Data.getData(rs) : null;
    }

    public static Data findByNoAndPwd(String no, String pwd)
            throws SQLException {
        PreparedStatement stmt =
            getPrepareStatement(
                    "SELECT " + Data.fields +
                    " FROM `" + table +
                    "` WHERE `no`=? AND password=?");
        stmt.setString(1, no);
        stmt.setString(2, getMD5(pwd.getBytes()));
        ResultSet rs = stmt.executeQuery();
        return rs.next() ? Data.getData(rs) : null;
    }

    public static boolean deleteById(int id) throws SQLException {
        PreparedStatement stmt = getPrepareStatement(
                "DELETE FROM `" + table +
                "` WHERE `id`=?");
        stmt.setInt(1, id);
        return stmt.executeUpdate() > 0;
    }

    public static class Data {
        private long id;
        private String no;
        private String name;
        private String password;
        private long _class;

        public final static String fields = "id,no,name,password,class";

        public static Data getData(ResultSet rs) throws SQLException {
            Data d = new Data();
            d.setId(rs.getInt(1));
            d.setNo(rs.getString(2));
            d.setName(rs.getString(3));
            d.setPassword(rs.getString(5));
            d.set_class(rs.getLong(5));
            return d;
        }
        
        public Data() {
            super();
        }

        public long getId() {
            return id;
        }
        public void setId(long id) {
            this.id = id;
        }

        public String getNo() {
            return no;
        }
        public void setNo(String no) {
            this.no = no;
        }

        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }

        public String getPassword() {
            return password;
        }
        public void setPassword(String password) {
            this.password = password;
        }

        public long get_class() {
            return _class;
        }
        public void set_class(long _class) {
            this._class = _class;
        }
    }
}
